USE [NANS21V]
GO

/****** Object:  StoredProcedure [dbo].[ForgeAheadList]    Script Date: 06/13/2014 15:24:10 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ForgeAheadList]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[ForgeAheadList]
GO

CREATE PROCEDURE [dbo].[ForgeAheadList]
	@TAIKAINO int,
	@KYOGINO int,
	@ROUNDNO int,
	@SUBNO int,
	@PLUS int,--0:全　1:進出者　2:+a
	@ALPHA int,
	@DIVERGE int, --0:トラック　1:投てき・高跳び・幅跳び
	@SYUMOKUKUBUN int,
	@SUBSYUMOKUKUBUN int,
	@SUBKIROKUKUBUN int
AS
	BEGIN
		DECLARE @SQL AS VARCHAR(MAX)

	SET @SQL = '
			select*from(
			SELECT DISTINCT '

		IF @DIVERGE = 1 
			BEGIN
				SET @SQL = @SQL + ' dbo.Kaze('+ STR(@SYUMOKUKUBUN) +','+ STR(@SUBSYUMOKUKUBUN) +', 
								CASE WHEN RE.KIROKU = 0 THEN '''' 
								ELSE dbo.Kiroku(RE.KIROKU,'+ STR(@SUBSYUMOKUKUBUN) +','+ STR(@SUBKIROKUKUBUN) +',(CASE WHEN CONVERT(varchar, RE.EXTEND1) = ''1'' then 3 else RE.EXTEND2 END)) END, RE.EXTEND1) AS KIROKUS,'
			END
		ELSE 
			BEGIN
				SET @SQL = @SQL + '
								CONVERT(varchar, CASE WHEN RE.KIROKU = 0 THEN '''' ELSE dbo.Kiroku(RE.KIROKU,SM.SYUMOKUKUBUN,SM.KIROKUKUBUN,(CASE WHEN ISNUMERIC(re.EXTEND2)  = 1 then re.EXTEND2 else 3 END)) END) + ''<br/>'' + CONVERT(varchar, RE.EXTEND1) AS KIROKUS,
								'
			END

	SET @SQL = @SQL + '		   
					CASE WHEN RE.PLUS = 1 THEN RE.JYUNI ELSE 99999 END AS PLUSJYUNI,
					CASE WHEN RE.EXTEND1 = '''' THEN 0 ELSE CONVERT(numeric(2,1) ,RE.EXTEND1)*1000 END AS EXTEND1,
					RE.RANKVAL,
					CASE WHEN RE.RANK= 0 THEN '''' ELSE RE.RANK END AS RANK,
					(CASE WHEN '+ STR(@SYUMOKUKUBUN) +' = 4 THEN CONVERT(varchar,KS.KYOGISYANO) ELSE (CONVERT(varchar,KS.NUMBER1) + CONVERT(varchar,KS.NUMBER2))END) AS NUMBER, 
					KS.KYOGISYANO,
					CONVERT(varchar, KS.KYOGISYAKANA) + ''<br/>''  +  CONVERT(varchar, KS.KYOGISYAMEI) AS KYOGISYA,
					CONVERT(varchar, S.SYOZOKUMEI) + ''<br/>'' + CONVERT(varchar, KS.KOJINSYOZOKUTIMEI) AS SYOZOKU,
					SK.OPENFLG, 
					LA.KUMINO,
					LA.LANE,
					dbo.Passage(RE.PLUS) AS PLUS,
					dbo.Jyuni(RE.JYUNI,SK.OPENFLG) AS JYUNI,
					(CASE WHEN RE.JYUNI IS NULL THEN NULL WHEN RE.JYUNI=0 THEN NULL ELSE RE.JYUNI END) AS Sort,
					RE.KIROKU AS KIROKU,
					LA.SYUKKETU,
					( CASE 
						WHEN RE.KIROKU IS NULL THEN 999999999.999
						WHEN RE.KIROKU =0 THEN 999999999.999 
						 ELSE RE.KIROKU END) AS KIROKU_MOTO  
					FROM ('
	IF @DIVERGE = 1 
		BEGIN
			SET @SQL = @SQL + '
					SELECT DISTINCT 
						RS.LANE,
						RS.SIGINO,
						RS.KIROKU AS SIGIVAL,
						RE.EXTEND1,
						RE.EXTEND2,
						RE.EXTEND3,
						RS.KUMINO,
						RE.KIROKU, 
						RE.JYUNI, 
						RE.PLUS,
						RE.RANK AS RANKVAL,
						CONVERT(varchar, RE.RANK) AS RANK,
						RE.RCOMMENT
					FROM TBL_RESULT_SIGI AS RS, 
						 TBL_RESULT AS RE 
					WHERE RS.TAIKAINO = '+ STR(@TAIKAINO) +'
						AND RS.KYOGINO = '+ STR(@KYOGINO) +'
						AND RS.ROUNDNO = '+ STR(@ROUNDNO) +'
						AND RS.SUBNO = '+ STR(@SUBNO) +'
						AND RS.TAIKAINO = RE.TAIKAINO 
						AND RS.KYOGINO = RE.KYOGINO 
						AND RS.SUBNO = RE.SUBNO 
						AND RS.ROUNDNO = RE.ROUNDNO 
						AND RS.KUMINO = RE.KUMINO 
						AND RS.LANE = RE.LANE'
			END
	ELSE 
		BEGIN
			SET @SQL = @SQL + '
					SELECT KUMINO,
						   LANE,
						   KIROKU,
						   JYUNI,
						   RCOMMENT,
						   PLUS,
						   RANK AS RANKVAL,
						   CONVERT(varchar, RANK) AS RANK,
						   EXTEND1,
						   EXTEND2,
						   EXTEND3,
						   EXTEND4,
						   EXTEND5
					 FROM TBL_RESULT
					 WHERE --KIROKU > 0 AND 
						TAIKAINO = '+ STR(@TAIKAINO) +'
						AND KYOGINO = '+ STR(@KYOGINO) +'
						AND SUBNO = '+ STR(@SUBNO) +'
						AND ROUNDNO = '+ STR(@ROUNDNO) +''
		END
	SET @SQL = @SQL + '
				) AS RE
				LEFT OUTER JOIN TBL_LANE AS LA
				ON RE.KUMINO = LA.KUMINO AND RE.LANE = LA.LANE,
					TBL_KYOGISYA AS KS,
					TBL_SYOZOKUTI AS ST,
					TBL_SYOZOKU AS S,
					TBL_KYOGI AS KY,
					TBL_SANKAKYOGI AS SK, 
					TBL_SYUMOKU_MASTER AS SM 
				WHERE	LA.TAIKAINO = KS.TAIKAINO
					AND LA.KYOGISYANO = KS.KYOGISYANO
					AND LA.TAIKAINO = SK.TAIKAINO 
					AND LA.KYOGISYANO = SK.KYOGISYANO 
					AND LA.KYOGINO = SK.KYOGINO 
					AND KS.TAIKAINO = S.TAIKAINO
					AND KS.SYOZOKUNO1 = S.SYOZOKUNO
					AND S.SYOZOKUTINO = ST.SYOZOKUTINO
					AND LA.TAIKAINO = '+ STR(@TAIKAINO) +'
					AND LA.KYOGINO = '+ STR(@KYOGINO) +'
					AND LA.SUBNO = '+ STR(@SUBNO) +'
					AND LA.ROUNDNO = '+ STR(@ROUNDNO) +'
					AND RE.PLUS >=  '+ STR(@PLUS) +'
					AND KY.TAIKAINO = LA.TAIKAINO 
					AND KY.KYOGINO = LA.KYOGINO 
					AND KY.SYUMOKUNO = SM.SYUMOKUNO 
					AND LA.ROUNDNO < 6
					AND LA.SYUKKETU <> 2
					) X
					'

		IF @DIVERGE = 1 
			BEGIN
				IF @ALPHA = 0
					IF @PLUS = 2
						SET @SQL = @SQL + ' ORDER BY CASE WHEN X.OPENFLG <> 0 THEN 1 ELSE 0 END,
													 CASE WHEN X.KIROKU = 0 OR X.KIROKU IS NULL THEN 1 ELSE 0 END ASC, 
													 X.KIROKU DESC, 
													 X.EXTEND1 ASC, 
													 X.KUMINO ASC, 
													 X.LANE ASC'
					ELSE  
						SET @SQL = @SQL + ' ORDER BY CASE WHEN X.OPENFLG <> 0 THEN 1 ELSE 0 END,
													 CASE WHEN X.KIROKU = 0 OR X.KIROKU IS NULL THEN 1 ELSE 0 END ASC, 
													 X.PLUSJYUNI ASC, 
													 X.KIROKU DESC, 
													 X.EXTEND1 ASC, 
													 X.KUMINO ASC, 
													 X.LANE ASC'
				ELSE IF @ALPHA = 1
					SET @SQL = @SQL + ' ORDER BY 
												CASE WHEN X.OPENFLG <> 0 THEN 1 ELSE 0 END, 
												X.KUMINO ASC, 
												X.Sort, 
												X.LANE ASC'
			END
					
			ELSE
		BEGIN
				IF @ALPHA = 0
					IF @PLUS = 2
						SET @SQL = @SQL + ' ORDER BY 
													 CASE WHEN X.OPENFLG <> 0 THEN 1 ELSE 0 END,
													 CASE WHEN X.KIROKU = 0 OR X.KIROKU IS NULL THEN 1 ELSE 0 END ASC, 
													 X.KIROKU ASC, 
													 X.EXTEND1 ASC, 
													 X.KUMINO ASC, 
													 X.LANE ASC'
					ELSE  
						SET @SQL = @SQL + ' ORDER BY  
													 CASE WHEN X.OPENFLG <> 0 THEN 1 ELSE 0 END,
													 CASE WHEN X.KIROKU = 0 OR X.KIROKU IS NULL THEN 1 ELSE 0 END ASC, 
													 X.PLUSJYUNI ASC, 
													 X.KIROKU ASC, 
													 X.EXTEND1 ASC, 
													 X.KUMINO ASC, 
													 X.LANE ASC'
				ELSE IF @ALPHA = 1
					SET @SQL = @SQL + ' ORDER BY 
												CASE WHEN X.OPENFLG <> 0 THEN 1 ELSE 0 END, 
												X.KUMINO ASC, 
												X.Sort, 
												X.LANE ASC'
			END

	--PRINT @SQL
	EXECUTE(@SQL)
END
GO

